﻿--
--
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'fnEAN128' AND type = 'TF')
BEGIN
  DROP FUNCTION fnEAN128
END
GO

-- UCC/EAN-128 parse
--
--
CREATE FUNCTION fnEAN128
(@EAN128 VARCHAR(128))
RETURNS @RC TABLE
(
  AI01 VARCHAR(20),
  AI02 VARCHAR(20),
  AI15 VARCHAR(20),
  AI17 VARCHAR(20),
  AI31 NUMERIC(18,6),
  AI37 NUMERIC(18,6)
)
--##DEBUG##WITH ENCRYPTION
 AS
BEGIN
  DECLARE
    @N INTEGER
  DECLARE
    @AI VARCHAR(2),
    @AI01 VARCHAR(20),
    @AI02 VARCHAR(20),
    @AI15 VARCHAR(20),
    @AI17 VARCHAR(20),
    @AI31 NUMERIC(18,6),
    @AI37 NUMERIC(18,6)
  
  --
  WHILE (1 = 1) BEGIN

__LOOP__:
  
    SELECT @AI = SUBSTRING(@EAN128, 1, 2)
    
    IF (@AI = N'')
      BREAK
    
    -- GTIN 14
    IF (@AI = N'01') BEGIN
    
      SELECT @AI01 = SUBSTRING(@EAN128, 3, 14)
      
      SELECT @EAN128 = SUBSTRING(@EAN128, 17, 128)
      GOTO __LOOP__
    END

    -- GTIN 
    IF (@AI = N'02') BEGIN
    
      SELECT @AI02 = SUBSTRING(@EAN128, 3, 14)
      
      SELECT @EAN128 = SUBSTRING(@EAN128, 17, 128)
      GOTO __LOOP__
    END

    -- Best before
    IF (@AI = N'15') BEGIN
      SELECT @AI15 = SUBSTRING(@EAN128, 3, 6)
      
      SELECT @EAN128 = SUBSTRING(@EAN128, 9, 128)
      GOTO __LOOP__    
    END
   
    -- Expire date
    IF (@AI = N'17') BEGIN
      SELECT @AI17 = SUBSTRING(@EAN128, 3, 6)
      
      SELECT @EAN128 = SUBSTRING(@EAN128, 9, 128)
      GOTO __LOOP__    
    END

    -- Netto weight
    IF (@AI = N'31') BEGIN
          
      SELECT @AI31 = CONVERT(DECIMAL(18,6), SUBSTRING(@EAN128, 5, 6)) / POWER(10, CONVERT(DECIMAL(18,6), SUBSTRING(@EAN128, 3, 2)))
      
      SELECT @EAN128 = SUBSTRING(@EAN128, 11, 128)
      GOTO __LOOP__
    END

    -- Quantity
    IF (@AI = N'37') BEGIN
      SELECT @N = CHARINDEX(CHAR(29), SUBSTRING(@EAN128, 3, 8))

      IF (@N = 0) BEGIN
        SELECT @N = 8
        SELECT @AI37 = CONVERT(NUMERIC(18,6), SUBSTRING(@EAN128, 3, 8))
      END
      ELSE BEGIN
        SELECT @AI37 = CONVERT(NUMERIC(18,6), SUBSTRING(@EAN128, 3, @N - 1))
      END

      SELECT @EAN128 = SUBSTRING(@EAN128, @N + 3, 128)

      GOTO __LOOP__
    END

    -- Ismeretlen AI, akkor ennyi volt
    BREAK   
  END
  
  INSERT INTO @RC
    (AI01, AI02, AI15, AI17, AI31, AI37)
  VALUES 
    (@AI01, @AI02, @AI15, @AI17, @AI31, @AI37)
  
  RETURN
END
GO
